<?php

namespace sdk;

use mysqli;

class Db {

    private $mysqli;

    public function __construct($host, $username, $passwd, $schema) {
        // 建立数据库连接
        mysqli_report(MYSQLI_REPORT_ERROR);
        //mysqli_report(MYSQLI_REPORT_ALL);
        $this->mysqli = new mysqli($host, $username, $passwd, $schema);
        $this->mysqli->query("SET NAMES utf8");
    }

    public function __destruct() {
        $this->mysqli->close();
    }

    // 查找该用户是否曾经注册过
    public function isCustomerRegistered($openid) {
        $is_registered = false;
        $sql_string = 'SELECT customer_id FROM customer WHERE openid = ? ;';
        $stmt = $this->mysqli->prepare($sql_string);
        $stmt->bind_param('s', $openid);
        $stmt->execute();
        if ($stmt->fetch()) {
            $is_registered = true;
        }
        $stmt->close();

        return $is_registered;
    }

    // 保存用户信息
    public function saveCustomer($profile) {
        $sql_string = 'INSERT INTO customer(
            is_subscribed, 
            openid, 
            nickname,
            sex, 
            city, 
            country, 
            province, 
            language, 
            headimgurl, 
            subscribe_time, 
            remark)
            VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);';
        $stmt = $this->mysqli->prepare($sql_string);
        $stmt->bind_param('ississsssss', 
                $profile->subscribe, 
                $profile->openid, 
                mysql_escape_string($profile->nickname), 
                $profile->sex, $profile->city, 
                $profile->country, 
                $profile->province, 
                $profile->language, 
                mysql_escape_string($profile->headimgurl), 
                date(DATE_ATOM, $profile->subscribe_time), 
                mysql_escape_string($profile->remark));

        $stmt->execute();
        $stmt->close();
    }

    // 查找该商户是否曾经注册过
    public function getServerId($openid) {
        $server_id = null;
        $sql_string = 'SELECT server_id FROM server WHERE openid = ? ;';
        $stmt = $this->mysqli->prepare($sql_string);
        $stmt->bind_param('s', $openid);
        $stmt->execute();
        $stmt->bind_result($server_id);
        $stmt->fetch();
        $stmt->close();
        return $server_id;
    }

    // 查找该用户是否曾经注册过
    public function getCustomerId($openid) {
        $customer_id = null;
        $sql_string = 'SELECT customer_id FROM customer WHERE openid = ? ;';
        $stmt = $this->mysqli->prepare($sql_string);
        $stmt->bind_param('s', $openid);
        $stmt->execute();
        $stmt->bind_result($customer_id);
        $stmt->fetch();
        $stmt->close();
        return $customer_id;
    }
    
    // 返回该服务对应的图片id
    public function getMainImgByServiceId($service_id) {
        $main_img = null;
        $sql_string = 'SELECT main_img FROM service WHERE service_id = ? ;';
        $stmt = $this->mysqli->prepare($sql_string);
        $stmt->bind_param('i', $service_id);
        $stmt->execute();
        $stmt->bind_result($main_img);
        $stmt->fetch();
        $stmt->close();
        return $main_img;
    }        
    
    // 返回该服务对应的商店id
    public function getShopIdByServiceId($service_id) {
        $shop_id = null;
        $sql_string = 'SELECT shop_id FROM service WHERE service_id = ? ;';
        $stmt = $this->mysqli->prepare($sql_string);
        $stmt->bind_param('i', $service_id);
        $stmt->execute();
        $stmt->bind_result($shop_id);
        $stmt->fetch();
        $stmt->close();
        return $shop_id;
    }    
    
    // 保存商户信息
    public function saveServer($profile) {
        $sql_string = 'INSERT INTO server(
            is_subscribed, 
            openid, 
            nickname,
            sex, 
            city, 
            country, 
            province, 
            language,
            headimgurl, 
            subscribe_time, 
            remark)
            VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);';
        $stmt = $this->mysqli->prepare($sql_string);
        $stmt->bind_param('ississsssss', 
                $profile->subscribe, 
                $profile->openid, 
                mysql_escape_string($profile->nickname), 
                $profile->sex, 
                $profile->city, 
                $profile->country, 
                $profile->province, 
                $profile->language, 
                mysql_escape_string($profile->headimgurl), 
                date(DATE_ATOM, $profile->subscribe_time), 
                mysql_escape_string($profile->remark));
        $stmt->execute();
        $stmt->close();
    }

    // 设置商户所在的当前商店
    public function setServerCurrentShop($shop_id, $server_id) {
        $sql_str = 'UPDATE server SET current_shop = ? WHERE server_id = ?';
        $stmt = $this->mysqli->prepare($sql_str);
        $stmt->bind_param('ii', $shop_id, $server_id);
        $stmt->execute();
        $stmt->close();
    }

    // 设置商户所在的当前商店
    public function setCustomerCurrentShop($shop_id, $customer_id) {
        $sql_str = 'UPDATE customer SET current_shop = ? WHERE customer_id = ?';
        $stmt = $this->mysqli->prepare($sql_str);
        $stmt->bind_param('ii', $shop_id, $customer_id);
        $stmt->execute();
        $stmt->close();
    }
    
    // 初始化商店信息
    public function initShop($shop_id) {
        $sql_string = 'INSERT INTO shop(shop_id) VALUES(?);';
        $stmt = $this->mysqli->prepare($sql_string);
        $stmt->bind_param('i', $shop_id);
        $stmt->execute();
        $stmt->close();
    }

    // 录入商店信息
    public function createShop($shop_id, $shop) {
        $sql_string = 'UPDATE shop SET poi_id = ?,
                business_name = ?,
                branch_name = ?,
                address = ?,
                telephone = ?,
                categories = ?,
                city = ?,
                province = ?,
                offset_type = ?,
                longitude = ?,
                latitude = ?,
                photo_list = ?,
                introduction = ?,
                recommend = ?,
                special = ?,
                open_time = ?,
                avg_price = ?,
                district = ? 
                where shop_id = ?';
        $stmt = $this->mysqli->prepare($sql_string);
        $stmt->bind_param('isssssssiddsssssssi', 
                $shop['poi_id'], 
                $shop['business_name'], 
                $shop['branch_name'], 
                $shop['address'], 
                $shop['telephone'], 
                $shop['categories'][0], 
                $shop['city'], 
                $shop['province'], 
                $shop['offset_type'], 
                $shop['longitude'], 
                $shop['latitude'], 
                $shop['photo_list'][0], 
                $shop['introduction'], 
                $shop['recommend'], 
                $shop['special'], 
                $shop['open_time'], 
                $shop['avg_price'], 
                $shop['district'], 
                $shop_id);
        $stmt->execute();
        $stmt->close();
    }

    // 关联商户与商店
    public function followShop($server_id, $shop_id, $role) {
        $sql_str = 'INSERT INTO employ (server_id, shop_id, role) VALUES (
                ?, ?, ?);';
        $stmt = $this->mysqli->prepare($sql_str);
        $stmt->bind_param('sii', $server_id, $shop_id, $role);
        $stmt->execute();
        $stmt->close();
    }

    // 关联用户与商店
    public function joinShop($customer_id, $shop_id) {
        $sql_str = 'INSERT INTO member (customer_id, shop_id) VALUES (
                ?, ?);';
        $stmt = $this->mysqli->prepare($sql_str);
        $stmt->bind_param('si', $customer_id, $shop_id);
        $stmt->execute();
        $stmt->close();
    }
    
    // 创建设备
    public function createDevice($shop_id, $user_role, $type, $param) {
        $sql_str = 'INSERT INTO device (
                        shop_id, 
                        user_role, 
                        type, 
                        param_1, 
                        param_2, 
                        param_3,
                        param_4
                        ) VALUES (
                        ?, ?, ?, ?, ?, ?, ?);';
        $stmt = $this->mysqli->prepare($sql_str);
        $stmt->bind_param('iiissss', $shop_id, $user_role, $type, $param[0], $param[1], $param[2], $param[3]);
        $stmt->execute();
        $stmt->close();
    }

    // 返回商户角色，0：店主；1：店员
    public function getServerRole($server_id) {
        $sql_str = 'SELECT employ.role FROM employ INNER JOIN server '
                . 'ON employ.server_id = server.server_id WHERE server.server_id = ?;';
        $stmt = $this->mysqli->prepare($sql_str);
        $stmt->bind_param('i', $server_id);
        $stmt->execute();
        $role = -1;
        $stmt->bind_result($role);
        $stmt->fetch();
        $stmt->close();
        return $role;
    }

    // 返回图片服务器上的ImageFileId
    public function getImageFileIdList($shop_id) {
        // 返回二维码的图片Id
        $sql_str = "SELECT param_1 FROM device where type = 0 and shop_id = ?";
        $stmt = $this->mysqli->prepare($sql_str);
        $stmt->bind_param('i', $shop_id);
        $stmt->execute();
        $fileid = null;
        $stmt->bind_result($fileid);
        $fileid_list = array();
        while ($stmt->fetch()) {
            $fileid_list[] = $fileid;
        }
        $stmt->close();
        return $fileid_list;
    }

    // 开始服务
    public function startService($server_id) {
        $sql_str = 'INSERT INTO service (shop_id, server_id) 
            SELECT current_shop, server_id FROM server 
            WHERE server_id = ?;';
        $stmt = $this->mysqli->prepare($sql_str);
        $stmt->bind_param('i', $server_id);
        $stmt->execute();
        $stmt->close();
    }

    // 结束服务
    public function endService($service_id) {
        $sql_str = 'UPDATE service SET end_time = now() WHERE service_id = ?;';
        $stmt = $this->mysqli->prepare($sql_str);
        $stmt->bind_param('i', $service_id);
        $stmt->execute();
        $stmt->close();
    }
    
    // 更新服务图片
    public function updateServiceImage($service_id, $fileid) {
        $sql_str = 'UPDATE service SET main_img = ? WHERE service_id = ?;';
        $stmt = $this->mysqli->prepare($sql_str);
        $stmt->bind_param('si', $fileid, $service_id);
        $stmt->execute();
        $stmt->close();
    }    
    
    // 返回服务号
    public function getLatestServiceId($server_id) {
        $sql_str = 'SELECT service_id FROM service WHERE server_id = 
            ? ORDER BY start_time DESC LIMIT 1';
        $stmt = $this->mysqli->prepare($sql_str);
        $stmt->bind_param('i', $server_id);
        $stmt->execute();
        $service_id = -1;
        $stmt->bind_result($service_id);
        $stmt->fetch();   
        $stmt->close();
        return $service_id;
    }
    
    // 查看服务是否已经结束
    public function isServiceEnded($service_id) {
        if ($service_id <= 0) {
            return true;
        }
        $is_service_ended = false;
        $sql_str = 'SELECT end_time FROM service WHERE service_id = ?';
        $stmt = $this->mysqli->prepare($sql_str);
        $stmt->bind_param('i', $service_id);
        $stmt->execute();
        $end_time = null;
        $stmt->bind_result($end_time);
        $stmt->fetch();   
        $stmt->close();
        if ($end_time != null) {
            $is_service_ended = true;
        }
        return $is_service_ended;
    }    
}
